Imports System.Data.SqlClient
Imports System.Text

Friend Class DBExtendedProperties
    Inherits DBBase

    Public Sub UpdateDatabaseDescription( _
        ByVal lDatabaseID As Long, _
        ByVal sDescription As String)

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        UpdateProperty( _
                oDatabase.DecryptedConnectionString, _
                sDescription)
    End Sub

    Public Function GetDatabaseDescription( _
        ByVal lDatabaseID As Long) As String

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Return GetProperty( _
                    oDatabase.DecryptedConnectionString)
    End Function

    Public Sub UpdateLoginDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lLoginID As Long, _
        ByVal sDescription As String)

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oLogin As New SQLAutoDocLib.Login(lLoginID)
        oLogin.Load()

        UpdateProperty( _
                oDatabase.DecryptedConnectionString, _
                sDescription, _
                "USER", _
                oLogin.Login)
    End Sub

    Public Function GetLoginDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lLoginID As Long) As String

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oLogin As New Login(lLoginID)
        oLogin.Load()

        Return GetProperty( _
                    oDatabase.DecryptedConnectionString, _
                    "USER", _
                    oLogin.Login)
    End Function

    Public Sub UpdateFunctionDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lFunctionID As Long, _
        ByVal sDescription As String)

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oFunction As New SQLAutoDocLib.Function(lFunctionID)
        oFunction.Load()

        Dim oLogin As New SQLAutoDocLib.Login(oFunction.OwnerID)
        oLogin.Load()

        UpdateProperty( _
                oDatabase.DecryptedConnectionString, _
                sDescription, _
                "USER", _
                oLogin.Login, _
                "function", _
                oFunction.Name)
    End Sub

    Public Function GetFunctionDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lFunctionID As Long) As String

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oFunction As New SQLAutoDocLib.Function(lFunctionID)
        oFunction.Load()

        Dim oLogin As New Login(oFunction.OwnerID)
        oLogin.Load()

        Return GetProperty( _
                    oDatabase.DecryptedConnectionString, _
                    "USER", _
                    oLogin.Login, _
                    "function", _
                    oFunction.Name)
    End Function

    Public Sub UpdateSProcDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lSProcID As Long, _
        ByVal sDescription As String)

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oSProc As New SQLAutoDocLib.SProc(lSProcID)
        oSProc.Load()

        Dim oLogin As New SQLAutoDocLib.Login(oSProc.OwnerID)
        oLogin.Load()

        UpdateProperty( _
                oDatabase.DecryptedConnectionString, _
                sDescription, _
                "USER", _
                oLogin.Login, _
                "procedure", _
                oSProc.Name)
    End Sub

    Public Function GetSProcDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lSProcID As Long) As String

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oSProc As New SProc(lSProcID)
        oSProc.Load()

        Dim oLogin As New SQLAutoDocLib.Login(oSProc.OwnerID)
        oLogin.Load()

        Return GetProperty( _
                    oDatabase.DecryptedConnectionString, _
                    "USER", _
                    oLogin.Login, _
                    "procedure", _
                    oSProc.Name)
    End Function

    Public Sub UpdateViewDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lViewID As Long, _
        ByVal sDescription As String)

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oView As New SQLAutoDocLib.View(lViewID)
        oView.Load()

        Dim oLogin As New SQLAutoDocLib.Login(oView.OwnerID)
        oLogin.Load()

        UpdateProperty( _
                oDatabase.DecryptedConnectionString, _
                sDescription, _
                "USER", _
                oLogin.Login, _
                "view", _
                oView.Name)
    End Sub

    Public Function GetViewDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lViewID As Long) As String

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oView As New View(lViewID)
        oView.Load()

        Dim oLogin As New SQLAutoDocLib.Login(oView.OwnerID)
        oLogin.Load()

        Return GetProperty( _
                    oDatabase.DecryptedConnectionString, _
                    "USER", _
                    oLogin.Login, _
                    "view", _
                    oView.Name)
    End Function

    Public Sub UpdateTableDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lTableID As Long, _
        ByVal sDescription As String)

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oTable As New SQLAutoDocLib.Table(lTableID)
        oTable.Load()

        Dim oLogin As New SQLAutoDocLib.Login(oTable.OwnerID)
        oLogin.Load()

        UpdateProperty( _
                oDatabase.DecryptedConnectionString, _
                sDescription, _
                "USER", _
                oLogin.Login, _
                "table", _
                oTable.Name)
    End Sub

    Public Function GetTableDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lTableID As Long) As String

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oTable As New SQLAutoDocLib.Table(lTableID)
        oTable.Load()

        Dim oLogin As New SQLAutoDocLib.Login(oTable.OwnerID)
        oLogin.Load()

        Return GetProperty( _
                    oDatabase.DecryptedConnectionString, _
                    "USER", _
                    oLogin.Login, _
                    "table", _
                    oTable.Name)
    End Function

    Public Sub UpdateFieldDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lFieldID As Long, _
        ByVal sDescription As String)

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oField As New SQLAutoDocLib.Field(lFieldID)
        oField.Load()

        Dim oTable As New SQLAutoDocLib.Table(oField.TableID)
        oTable.Load()

        Dim oLogin As New SQLAutoDocLib.Login(oTable.OwnerID)
        oLogin.Load()

        UpdateProperty( _
                oDatabase.DecryptedConnectionString, _
                sDescription, _
                "USER", _
                oLogin.Login, _
                "table", _
                oTable.Name, _
                "column", _
                oField.Name)
    End Sub

    Public Function GetFieldDescription( _
        ByVal lDatabaseID As Long, _
        ByVal lFieldID As Long) As String

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()

        Dim oField As New SQLAutoDocLib.Field(lFieldID)
        oField.Load()

        Dim oTable As New SQLAutoDocLib.Table(oField.TableID)
        oTable.Load()

        Dim oLogin As New SQLAutoDocLib.Login(oTable.OwnerID)
        oLogin.Load()

        Return GetProperty( _
                    oDatabase.DecryptedConnectionString, _
                    "USER", _
                    oLogin.Login, _
                    "table", _
                    oTable.Name, _
                    "column", _
                    oField.Name)
    End Function

    Private Sub UpdateProperty( _
                    ByVal sConnection As String, _
                    ByVal sDescription As String, _
                    Optional ByVal sLevel0Type As String = "default", _
                    Optional ByVal sLevel0Name As String = "default", _
                    Optional ByVal sLevel1Type As String = "default", _
                    Optional ByVal sLevel1Name As String = "default", _
                    Optional ByVal sLevel2Type As String = "default", _
                    Optional ByVal sLevel2Name As String = "default")

        UpdateExtendedProperty( _
                    sConnection, _
                    sDescription, _
                    sLevel0Type, _
                    sLevel0Name, _
                    sLevel1Type, _
                    sLevel1Name, _
                    sLevel2Type, _
                    sLevel2Name)
    End Sub

    Private Sub UpdateExtendedProperty( _
                ByVal sConnection As String, _
                ByVal sValue As String, _
                ByVal sLevel0Type As String, _
                ByVal sLevel0Name As String, _
                ByVal sLevel1Type As String, _
                ByVal sLevel1Name As String, _
                ByVal sLevel2Type As String, _
                ByVal sLevel2Name As String)

        If Not ExecuteExtendedProperty( _
                sConnection, _
                "sp_updateextendedproperty", _
                sValue, _
                sLevel0Type, _
                sLevel0Name, _
                sLevel1Type, _
                sLevel1Name, _
                sLevel2Type, _
                sLevel2Name) Then

            ExecuteExtendedProperty( _
                        sConnection, _
                        "sp_addextendedproperty", _
                        sValue, _
                        sLevel0Type, _
                        sLevel0Name, _
                        sLevel1Type, _
                        sLevel1Name, _
                        sLevel2Type, _
                        sLevel2Name)
        End If

    End Sub

    Private Function ExecuteExtendedProperty( _
                        ByVal sConnection As String, _
                        ByVal sCommand As String, _
                        ByVal sValue As String, _
                        ByVal sLevel0Type As String, _
                        ByVal sLevel0Name As String, _
                        ByVal sLevel1Type As String, _
                        ByVal sLevel1Name As String, _
                        ByVal sLevel2Type As String, _
                        ByVal sLevel2Name As String) As Boolean

        Dim bSuccess As Boolean = True

        Dim sSQL As New StringBuilder("exec " & sCommand)
        With ssql
            .Append(" @name='MS_Description',")
            .Append(" @value=" & ValueToDBValue(sValue) & ",")

            .Append(" @level0type=" & ExtendedPropertiesDefault(sLevel0Type) & ",")
            .Append(" @level0name=" & ExtendedPropertiesDefault(sLevel0Name) & ",")

            .Append(" @level1type=" & ExtendedPropertiesDefault(sLevel1Type) & ",")
            .Append(" @level1name=" & ExtendedPropertiesDefault(sLevel1Name) & ",")

            .Append(" @level2type=" & ExtendedPropertiesDefault(sLevel2Type) & ",")
            .Append(" @level2name=" & ExtendedPropertiesDefault(sLevel2Name))
        End With

        Try
            MyBase.ExecuteNonQuery(sSQL.ToString, sConnection)
        Catch oEX As SqlException
            bSuccess = False
        End Try

        Return bSuccess
    End Function

    Private Function ExtendedPropertiesDefault(ByVal sValue) As String
        If sValue = "default" Then
            Return sValue
        Else
            Return ValueToDBValue(sValue)
        End If
    End Function
    Private Function GetProperty( _
                ByVal sConnection As String, _
                Optional ByVal sLevel0Type As String = "default", _
                Optional ByVal sLevel0Name As String = "default", _
                Optional ByVal sLevel1Type As String = "default", _
                Optional ByVal sLevel1Name As String = "default", _
                Optional ByVal sLevel2Type As String = "default", _
                Optional ByVal sLevel2Name As String = "default") As String

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append(" [objname],")
            .Append(" [value]")
            .Append(" from")
            .Append(" ::fn_listextendedproperty (")
            .Append("'MS_Description',")
            .Append("'USER',")
            .Append("default,")
            .Append("default,")
            .Append("default,")
            .Append("default,")
            .Append("default)")
        End With

        Return MyBase.ExecuteScalar(sSQL.ToString, sConnection)
    End Function
End Class
